﻿using System.Collections.Generic;
using System.Linq;
using System.Text;
using QLDBHYT.VO;
using System.Data;
using System.Data.SqlClient;
namespace QLDBHYT.DAO
{
    class P1hDAO
    {
        private dbConnection conn;
        public P1hDAO()
        {
            conn = new dbConnection();
        }
        public DataTable SearhMathe(string mathe)
        {
            string query = string.Format("select top 1 * from ( "
            + " select ho_ten,gioi_tinh,nam_sinh,dia_chi,tinh_kcb,ma_kcb,noi_kham,"
            + " gtri_tu,gtri_den,ngay_tt from dbo.p1_h where mathe = @mathe"
            + " and ngay_tt = (select max(ngay_tt) from dbo.p1_h where mathe = @mathe)"
            + " and gtri_den >= getdate()"
            + " union all"
            + " select ho_ten,gioi_tinh,nam_sinh,dia_chi,tinh_kcb,ma_kcb,noi_kham,"
            + " gtri_tu,gtri_den,ngay_tt from dbo.p2_h where mathe = @mathe"
            + " and ngay_tt = (select max(ngay_tt) from dbo.p2_h where mathe = @mathe)"
            + " and gtri_den >= getdate()"
            + " ) as t"
            + " order by ngay_tt desc");
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@mathe", SqlDbType.NVarChar);
            sqlParameters[0].Value = mathe;
            
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchKhoa()
        {
            string query = string.Format("select ma_khoa,ten_khoa from khoakham");
            SqlParameter[] sqlParameters = new SqlParameter[0];
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchTenHieu(int thang, int nam)
        {
            string query = string.Format("select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,"
            + " a.duongdung as bietduoc,a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh,"
            + " isnull(c.sldauky,0) as sldauky,isnull(c.slnhap,0) as slnhap,isnull(sum(slxuat),0) as slxuat,isnull(slxuatdc,0) as slxuatdc,"
            + " isnull(c.sldauky,0) + isnull(c.slnhap,0) - isnull(sum(slxuat),0) - isnull(slxuatdc,0) as slcuoiky"
            + " from dm a left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu "
            + " left join NT_PHIEUNHAP c on a.stt_nh = c.stt_nh and a.ma_hieu = c.ma_hieu"
            + " and c.thang = @thang and c.nam = @nam"
            + " left join NT_PHIEUXUAT d on a.stt_nh = d.stt_nh and a.ma_hieu = d.ma_hieu"
            + " and d.thang = @thang and d.nam = @nam "
            + " where  b.gia <> 0 " 
            + " group by a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,"
            + " c.sldauky,c.slnhap,slxuatdc,a.duongdung,a.lieudung "
            + " order by ten_hieu");
            SqlParameter[] sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = thang;
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value =nam;
            
            return conn.executeSelectQuery(query, sqlParameters);
        }
    }
}
